from django.shortcuts import render
# 导入sqlhelper
from utils import sqlhelper
# 导入json格式
from django.http import JsonResponse


def index(request):
    return render(request, 'weatherweb/cityurl.html')


def list_values(request):
    """获取数据的后端接口"""
    # 接收传递过来的查询字符串
    query_str = request.POST.get('queryStr', '')
    selected = 1 if request.POST.get('citySelected') == 'true' else 0
    # 获取传递过来的page和limit
    page = int(request.POST.get('page'))
    limit = int(request.POST.get('limit'))
    # 准备SQL语句
    sql = "Select Id, Province, City, Area, Url from CityURL " \
          "where (Province Like '%s' Or City Like '%s' Or Area Like '%s') " % (
              '%' + query_str + '%', '%' + query_str + '%', '%' + query_str + '%')

    if selected:
        sql += 'and City=Area;'
    # 执行
    response = sqlhelper.get_db_data_dict(sql, ['id', 'province', 'city', 'area', 'url'])
    # 定义一个返回的数据结构
    res = {'code': -1, 'count': 0, 'msg': '', 'data': ''}
    # 判断
    if response['status']:
        res['code'] = 0  # 表示成功！
        res['count'] = len(response['data'])
        res['data'] = response['data'][(page - 1) * limit: page * limit]
    else:
        res['msg'] = response['error']

    # 返回
    return JsonResponse(res, safe=False, json_dumps_params={'ensure_ascii': False})


def add_value(request):
    """实现添加"""
    # 接收传递过来的值
    rec = request.POST
    # 准备SQL语句
    sql = "Insert Into CityURL(Province, City,Area,URL) Values ('%s','%s','%s','%s')" \
          % (rec['province'], rec['city'], rec['area'], rec['url'])
    # 执行
    response = sqlhelper.update_db(sql)
    # 判断
    if response['status']:
        return JsonResponse({'status': True})
    else:
        return JsonResponse({'status': False, 'error': response['error']})


def edit_value(request):
    """修改记录"""
    # 接收传递过来的数据
    rec = request.POST
    # 准备SQL语句
    sql = "Update CityURL Set Province = '%s', City='%s', Area='%s', Url ='%s' Where Id='%s'" % \
          (rec['province'], rec['city'], rec['area'], rec['url'], rec['url_id'])
    # 执行
    # 执行
    response = sqlhelper.update_db(sql)
    # 判断
    if response['status']:
        return JsonResponse({'status': True})
    else:
        return JsonResponse({'status': False, 'error': response['error']})


def del_value(request):
    """删除"""
    # 接收传递过来的值
    id = request.POST.get('id')
    # 准备SQL语句
    sql = "Delete From CityURL Where Id ='%s'" %(id)
    # 执行
    response = sqlhelper.update_db(sql)
    # 判断
    if response['status']:
        return JsonResponse({'status': True})
    else:
        return JsonResponse({'status': False, 'error': response['error']})


def get_province(request):
    """获取省"""
    # sql语句
    sql = "Select distinct Province from CityURL;"
    # 获取数据
    response = sqlhelper.get_db_data_dict(sql, ['province'])
    # 返回
    return JsonResponse({'data': response['data']})

def get_citys_by_province(request):
    # 获取传递过来的省
    q_str= request.POST.get('province')
    # sql语句
    sql = "Select distinct City from CityURL where Province = '%s';" %(q_str)
    # 获取数据
    response = sqlhelper.get_db_data_dict(sql, ['city'])
    # 返回
    return JsonResponse({'data': response['data']})